import pandas as pd
import requests
import json

def fetch_data():
    url = "https://sqadmin.mot.gov.cn/pczz/api/zbquery/query"
    headers = {
        "Accept": "application/json, text/plain, */*",
        "Accept-Encoding": "gzip, deflate, br, zstd",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6",
        "Authorization": "65bee8b8-0e75-46dd-8f8f-3ba0e214436c",
        "Cache-Control": "no-cache",
        "Connection": "keep-alive",
        # "Content-Length": "48166",
        "Content-Type": "application/json",
        "Host": "sqadmin.mot.gov.cn",
        "Origin": "https://sqadmin.mot.gov.cn",
        "Pragma": "no-cache",
        "Referer": "https://sqadmin.mot.gov.cn/pczz/",
        "Sec-Fetch-Dest": "empty",
        "Sec-Fetch-Mode": "cors",
        "Sec-Fetch-Site": "same-origin",
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.0.0.0 Safari/537.36 Edg/124.0.0.0",
        "X-Requested-With": "XMLHttpRequest",
        "language": "zh-CN",
        "sec-ch-ua": '"Chromium";v="124", "Microsoft Edge";v="124", "Not-A.Brand";v="99"',
        "sec-ch-ua-mobile": "?0",
        "sec-ch-ua-platform": '"Windows"'
    }

    payload = {
        "title": "项目自查一般问题隐患明细",
        "queryModel": {
            "queryObjects": [
                {
                    "id": "nrfs_dim_R",
                    "name": "NR_PERIOD_R",
                    "fullName": "NR_PERIOD_R",
                    "schemeName": "QYZC",
                    "title": "日",
                    "type": "GROUP",
                    "children": [
                        {
                            "id": "nrfs_dim_R_dimattr_P_TIMEKEY",
                            "name": "P_TIMEKEY",
                            "fullName": "NR_PERIOD_R.P_TIMEKEY",
                            "schemeName": "QYZC",
                            "title": "时期",
                            "type": "DIMENSIONATTRIBUTE",
                            "dataType": 6,
                            "timekey": True,
                            "periodType": "DAY"
                        }
                    ]
                },
                {
                    "id": "nrfs_dim_MD_ORG_FXYH@ORG",
                    "name": "MD_ORG_FXYH",
                    "fullName": "MD_ORG_FXYH",
                    "schemeName": "QYZC",
                    "title": "风险隐患",
                    "type": "GROUP",
                    "children": [
                        {
                            "id": "nrfs_dim_MD_ORG_FXYH@ORG_dimattr_62e24ecd-8061-192d-ae8f-ab7b204c6e10",
                            "name": "ORGCODE",
                            "fullName": "MD_ORG_FXYH.ORGCODE",
                            "schemeName": "QYZC",
                            "title": "代码",
                            "alias": "单位代码",
                            "type": "DIMENSIONATTRIBUTE",
                            "dataType": 6
                        },
                        {
                            "id": "nrfs_dim_MD_ORG_FXYH@ORG_dimattr_68f181b3-de2d-c0c9-9ed9-4b259f34de17",
                            "name": "NAME",
                            "fullName": "MD_ORG_FXYH.NAME",
                            "schemeName": "QYZC",
                            "title": "名称",
                            "alias": "单位名称",
                            "type": "DIMENSIONATTRIBUTE",
                            "dataType": 6
                        }
                    ]
                },
                {
                    "id": "nrfs_field_69ffa948-796b-4aeb-9b61-649456f5f721",
                    "name": "XMDM",
                    "fullName": "XMZC_XZYBWTYHMX.XMDM",
                    "schemeName": "QYZC",
                    "title": "项目代码",
                    "type": "ZB",
                    "dataType": 6
                },
                {
                    "id": "nrfs_field_ba97a0ba-33e5-42e4-8cd9-7d9d3d41cfb6",
                    "name": "XMMC",
                    "fullName": "XMZC_XZYBWTYHMX.XMMC",
                    "schemeName": "QYZC",
                    "title": "项目名称",
                    "type": "ZB",
                    "dataType": 6
                },
                {
                    "id": "nrfs_field_4f50f806-e02b-46e1-b6e0-774341ec1dff",
                    "name": "SSLY",
                    "fullName": "XMZC_XZYBWTYHMX.SSLY",
                    "schemeName": "QYZC",
                    "title": "所属领域",
                    "type": "ZB",
                    "dataType": 6
                },
                {
                    "id": "nrfs_field_22e6b41f-be54-4521-a6a0-47e742f5f1a7",
                    "name": "BDMC",
                    "fullName": "XMZC_XZYBWTYHMX.BDMC",
                    "schemeName": "QYZC",
                    "title": "标段名称",
                    "type": "ZB",
                    "dataType": 6
                },
                {
                    "id": "nrfs_field_38a1323b-f18b-458b-b324-11808d304799",
                    "name": "WTYHMS",
                    "fullName": "XMZC_XZYBWTYHMX.WTYHMS",
                    "schemeName": "QYZC",
                    "title": "问题隐患描述",
                    "type": "ZB",
                    "dataType": 6
                },
                {
                    "id": "nrfs_field_90fcac79-7b8e-43e4-af51-62d281ee5c87",
                    "name": "FXRQ",
                    "fullName": "XMZC_XZYBWTYHMX.FXRQ",
                    "schemeName": "QYZC",
                    "title": "发现日期",
                    "type": "ZB",
                    "dataType": 2
                },
                {
                    "id": "nrfs_field_853316d5-2d37-406a-bf35-840d1ab3eddb",
                    "name": "ZGQX",
                    "fullName": "XMZC_XZYBWTYHMX.ZGQX",
                    "schemeName": "QYZC",
                    "title": "整改期限",
                    "type": "ZB",
                    "dataType": 5
                },
                {
                    "id": "nrfs_field_247cfaaf-b826-46d8-8662-b3c145b47195",
                    "name": "SFWCZG",
                    "fullName": "XMZC_XZYBWTYHMX.SFWCZG",
                    "schemeName": "QYZC",
                    "title": "是否完成整改",
                    "type": "ZB",
                    "dataType": 6
                }
            ],
            "conditions": [
                {
                    "fullName": "NR_PERIOD_R",
                    "objectType": "DIMENSION",
                    "conditionType": "RANGE",
                    "defaultValues": ["20250106"]
                },
                {
                    "fullName": "MD_ORG_FXYH",
                    "objectType": "DIMENSION",
                    "conditionType": "MULTIPLE",
                    "defaultValues": [
                        "330700", "330702", "330703", "330723", "330726", "330727", "330781", "330782", "330783", "330784"
                    ]
                }
            ]
        },
        "pageInfo": {
            "pageSize": 50,
            "pageIndex": 1
        }
    }

    response = requests.post(url, headers=headers, json=payload)
    if response.status_code == 200:
        try:
            res = response.json()
            print("获取的数据:", res.get('data', '无数据'))
            if 'data' in res and 'cellBook' in res['data'] and 'sheets' in res['data']['cellBook']:
                return res['data']['cellBook']['sheets']
            else:
                print("数据不存在")
                return None
        except json.JSONDecodeError:
            print("响应不是有效的JSON格式")
            return None
    else:
        print("请求失败，状态码:", response.status_code)
        return None


data = fetch_data()

# 提取表头
headers = [col['v'] for col in data[0]['data'][0]]

# 提取数据（跳过表头行）
data_rows = [[cell['v'] for cell in row] for row in data[0]['data'][1:]]

# 将数据转换为DataFrame
df = pd.DataFrame(data_rows, columns=headers)

# 删除"排序字段"列
df = df.drop(columns=["排序字段"])

# 保存为Excel文件
df.to_excel('./Pyjd/重大隐患排查系统/output.xlsx', index=False)
